﻿DECLARE @DATE_END_MONTH NVARCHAR(100)=(SELECT MAX_DATE_ENDMONTH FROM [DATE_EM_REPORT])
DECLARE @LAST_MONTH NVARCHAR(6) = CONVERT(NVARCHAR(6),DATEADD(M,-1,CONVERT(DATE,@DATE_END_MONTH)),112)
--PRINT @LAST_MONTH
DECLARE @MONTH NVARCHAR(100) = (SELECT MAX_MONTH FROM DATE_EM_REPORT)



PRINT ('DANH SACH BADBANK')
EXEC('
DROP TABLE TBL_LIST_BAD_BANK

SELECT [STT]
        ,[CIF]
        ,[CUSNAME]
        ,[SEGMENT]
        ,[MOVING_IN_DATE]
        ,[BOOK]
        ,[MOVING_OUT_DATE]
        ,[MATURITY_DATE]
        ,[OUT SOURCE/AMC]
        ,[NGÀY CHUYỂN OUT SOURCE/ AMC]
        ,[GHI CHÚ] INTO TBL_LIST_BAD_BANK
FROM GIANGLH3.RISK.[DBO].BADBANK_SME_'+@DATE_END_MONTH+' A
WHERE SEGMENT=''KHDN''
AND (MATURITY_DATE IS NULL AND MOVING_OUT_DATE IS  NULL)

ALTER TABLE TBL_LIST_BAD_BANK 
ADD DAO NVARCHAR(MAX), VPB_INDUSTRY NVARCHAR(255), VPB_INDUSTRY_NAME NVARCHAR(255), BRANCH_ID NVARCHAR(11), DAO_NAME NVARCHAR(255)

UPDATE A
SET A.DAO=B.DAO,A.VPB_INDUSTRY=B.INDUSTRY_NAME_EN,A.VPB_INDUSTRY_NAME=B.INDUSTRY_NAME, A.BRANCH_ID = B.BRANCH_ID, A.DAO_NAME = B.DAO_NAME
FROM TBL_LIST_BAD_BANK A, TBL_CUSTOMER B
WHERE A.CIF  = B.CIF

DELETE FROM TBL_LIST_BAD_BANK
WHERE CIF IS NULL')


update b
set b.date_data = (select MAX_DATE_ENDMONTH from DATE_EM_REPORT)
from TBL_DATE_BUSINESS b
where b.name_table = 'TBL_LIST_BAD_BANK'

--select * from GIANGLH3.RISK.[DBO].BADBANK_SME_20170331

--delete from server12.SMECustomer360.[dbo].TBL_ALERT_BAD_BANK
--insert server12.SMECustomer360.dbo.TBL_ALERT_BAD_BANK
--select * from TBL_ALERT_BAD_BANK
--select * from GIANGLH3.TAICAPNHANH.[DBO].[BOTTOMUP_luyke]

--delete from server12.SMECustomer360.[dbo].TBL_ALERT_BADBANK_BOTTOMUP
--insert server12.SMECustomer360.dbo.TBL_ALERT_BADBANK_BOTTOMUP
--select * from TBL_ALERT_BADBANK_BOTTOMUP


--delete from server12.SMECustomer360.[dbo].TBL_ALERT_BAD_BANK_WATCHLIST
--insert server12.SMECustomer360.dbo.TBL_ALERT_BAD_BANK_WATCHLIST
--select * from TBL_ALERT_BAD_BANK_WATCHLIST
--SELECT * FROM TBL_ALERT_BADBANK_BOTTOMUP
--declare @date_update nvarchar(100)= (select [DATE_UPDATE] from [DATE_EM_REPORT])

--select ROW_NUMBER() OVER(PARTITION BY B.CUSTOMER_ID ORDER BY B.Contract_no DESC) AS RN, * 
--into s16_g3b_raw
--from server16.vpb_g3b.[dbo].[TBL_G3B_T24CRD] b 
--where BUSINESS_DATE = @date_update
--				AND BRANCH_CODE_GBBB = 'VN9990001' 
--				AND DATE_GBBB is not null and EXISTS (SELECT *
--            FROM   TBL_CUSTOMERS A 
--            WHERE A.CIF = B.CUSTOMER_ID)  

--select date_gbbb,* 
--from s16_g3b_raw 
--where rn = 1 
--and date_gbbb > '20170301'